﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.Linq;
using System.Text;

using System.IO;


using Microsoft.Office.Interop.Excel;
namespace NovoFileIO
{
    public class Novo_Excel
    {
        public Novo_Excel(){}

       #region OleDb
        /// <summary>
        /// Use OleDb method for excel importing, which need to install AccessDatabaseEngine.exe or AccessDatabaseEngine_X64.exe(Depends on your PC OS version).
        /// Each sheet in the excel file will be imported as data table which includes in returned dataset type parameter.
        /// </summary>
        /// <param name="fileName"> excel file name which include full path</param>
        /// <returns>Excel sheets and data returns as dataset type </returns>
        public DataSet Import_OleDb(string fileName)
        {
            //Excel的连接
            OleDbConnection objConn = null;
            //数据表
            DataSet ds = new DataSet();
            try
            {
                System.Data.DataTable dtExcel = new System.Data.DataTable();
                //获取文件扩展名
                string strExtension = System.IO.Path.GetExtension(fileName);
                string strFileName = System.IO.Path.GetFileName(fileName);

                // with Novo PC, should rename the excel file extension to .txt or .raw after decrpthion.
                switch (strExtension)
                {
                    case ".xls":
                    case ".txt":    
                        objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                            fileName + ";" + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"");
                        break;
                    case ".xlsx":
                    case ".raw":
                        objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                            fileName + ";" + "Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"");
                        break;
                    default:
                        objConn = null;
                        break;
                }

                if (objConn == null)
                {
                    return null;
                }
                objConn.Open();

                // Get all sheet name in the excel                
                ArrayList result = new ArrayList { };
                System.Data.DataTable sheetNames = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                    new object[] { null, null, null, "TABLE" });
                foreach (DataRow dr in sheetNames.Rows)
                {
                    result.Add(dr[2]);  //Get sheet name
                }

                // Get all datatable from each sheet and add to ds.
                ds.Clear();
                string strSql;
                foreach (string tName in result)
                {
                    strSql = "select * from [" + tName + "]";
                    OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                    OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
                    myData.Fill(ds, tName);//填充数据
                }
                objConn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (objConn.State == ConnectionState.Open)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
            }

            if (ds.Tables.Count > 0)
            {
                return ds;
            }
            else
            {
                return null;
            }
        }
        #endregion OleDb

        #region Interop - Can read/write any cell directly, very flexible, but much slower than OleDb
        public DataSet Import_Interop(string fileName)
        {
            Application app = new Application();
            Sheets sheets;
            Workbook workbook = null;
            object oMissiong = System.Reflection.Missing.Value;

            DataSet dataSet = new DataSet();
            string cellContent;
            try
            {
                if (app == null)
                {
                    return null;
                }
                workbook = app.Workbooks.Open(fileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);

                sheets = workbook.Worksheets;
                for (int ix = 1; ix <= sheets.Count; ix++)
                {
                    System.Data.DataTable dt = new System.Data.DataTable();
                    Worksheet workSheet = (Worksheet)sheets.get_Item(ix);

                    for (int colIx = 1; colIx <= workSheet.UsedRange.Columns.Count; colIx++)
                    {
                        dt.Columns.Add();
                        //Range _range = (Range)workSheet.Cells[1, colIx];
                        //if (_range.Text.ToString().Trim() == "")
                        //    dt.Columns.Add(colIx.ToString());
                        //else
                        //    dt.Columns.Add(_range.Text.ToString().Trim());

                    }
                    for (int rowIx = 1; rowIx <= workSheet.UsedRange.Rows.Count; rowIx++)
                    {
                        DataRow dr = dt.NewRow();
                        for (int colIx = 1; colIx <= workSheet.UsedRange.Columns.Count; colIx++)
                        {
                            Range _range = (Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIx, colIx];
                            cellContent = (_range.Value2 == null) ? "" : _range.Text.ToString().Trim();
                            dr[colIx - 1] = cellContent;
                        }
                        dt.Rows.Add(dr);
                    }
                    dataSet.Tables.Add(dt);
                }
            }
            finally
            {
                workbook.Close(false, oMissiong, oMissiong);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return dataSet;
        }

        // TBD
        public Workbook Read(string filename)
        {
            return null;
            
        }

        // TBD
        public bool Write(string filename, Workbook wb) 
        { 
            return false; 
        }

        #endregion Interop

        public void Novo_CSV_Write(string filename, string content) 
        {
            StreamWriter sw = new StreamWriter(filename, true);
            sw.Write(content);
            sw.Close();           
        }

    }

    public class Novo_TXT
    {
        public Novo_TXT() { }

        public void Novo_TXT_Write(string filename, string content)
        {
            StreamWriter sw = new StreamWriter(filename, true);
            sw.WriteLine(content);
            sw.Close();

        }
    }




}
